﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Text;
using OfficeOpenXml;

using Analysis.DAL;
using Analysis.DAL.Repositories;
using Analysis.Entities;
using Analysis.Code;
using System.Web.Security;

namespace Analysis
{
    public partial class AP : BaseGridPage
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!AccessConfirm("Адресная программа"))
            {
                throw new HttpException(401, "Доступ закрыт");
            }
            if (!IsPostBack)
                LoadData();

            if (IsAdmin)
            {
                btnCustomColumn.Visible = true;
            }
        }

        protected void Page_Init(object sender, EventArgs e)
        {
            PageName = "AP";
            FileName = "Адресная-программа";

            if (!IsPostBack)
                LoadDataFromDb();

            InitGrid();
        }

        public override void LoadDataFromDb()
        {
            // Данные из БД
            GridData = APRepository.FindAP(CurrentUserId);

            // Формируем список кастомных колонок
            GenerateCustomColumnList();
            
            // Добавляем в источник данных служебные поля
            PrepareDataTable();
        }

        public override void LoadData()
        {
            /*DataTable gridSource = GridData.Copy();
            if (gridSource.Rows.Count == 0)
            {
                gridSource.Rows.Add(gridSource.NewRow());
                gridSource.AcceptChanges();
            }*/

            DataView dv = GridDataView;
            if (dv.Count == 0)
            {
                IsEmptyGrid = true;
                dv.AddNew();
            }
            grid.DataSource = dv;
            grid.DataBind();
            upMain.Update();

            // Привязываем грид редактора колонок
            ctrlCustomColumnDialog.LoadData(ColumnsList);
        }

        public override GridView GetGrid()
        {
            return grid;
        }

        protected override UpdatePanel GetUP()
        {
            return upMain;
        }

        protected override void InitGrid()
        {
            TemplateField tf = null;
            Depart depart = null;

            // Настройка колонок юрезов на должности: прячем незадействованные должности
            for (int i = 1; i <= 5; ++i)
            {
                string nameColumn = "Depart" + i.ToString();
                tf = GetColumnByHeader(nameColumn);
                if (tf == null)
                    continue;

                using (var repo = RepositoryFactory.ResolveRepository<SetupRepository>())
                {
                    depart = repo.FindDepartByColumnName(nameColumn);
                }

                if (depart == null)
                    tf.Visible = false;
                else
                    tf.HeaderText = depart.DepartName;
            }

            base.InitGrid();
        }

        protected override void grid_RowCreated(Object sender, GridViewRowEventArgs e)
        {
            base.grid_RowCreated(sender, e);

            if ((e.Row.RowType == DataControlRowType.DataRow) || (e.Row.RowType == DataControlRowType.Header) || (e.Row.RowType == DataControlRowType.Footer))
            {
                // Перемещение кастомных колонок
                int iChannel = GetColumnIndexByHeader("Канал");
                int iAction = GetColumnIndexByHeader("Действия");
                GridViewRow r = e.Row;

                for (int i = iAction + 1; i < r.Cells.Count; i = iAction + 1)
                {
                    TableCell cell = r.Cells[i];
                    r.Cells.Remove(cell);
                    r.Cells.AddAt(iChannel, cell);
                    ++iChannel;
                    ++iAction;
                }
            }

            if (e.Row.RowType == DataControlRowType.Header)
            {
                int iChannel = ColumnsList.FindIndex(i => i.HeaderText.Equals("Канал"));
                for (int i = 0; i < ColumnsList.Count; ++i)
                {
                    ColumnInfo item = ColumnsList[i];
                    if (item.IsCustom)
                    {
                        ColumnsList.Remove(item);
                        ColumnsList.Insert(iChannel, item);
                        ++iChannel;
                    }
                }
            }
        }

        protected override void grid_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            base.grid_RowDataBound(sender, e);

            DataRowView drv = (DataRowView)e.Row.DataItem;

            if (e.Row.RowType == DataControlRowType.Header)
            {
                StringBuilder sb = new StringBuilder();

                // список автокомплита для региона и города
                DropDownList ddl = (DropDownList)e.Row.FindControl("ddlFilter2");
                if (ddl != null)
                {
                    sb.Append("autoRegion = [");
                    for (int i = 1; i < ddl.Items.Count; ++i)
                    {
                        if (i != 1)
                            sb.Append(",");
                        sb.Append("\"" + ddl.Items[i].Text + "\"");
                    }
                    sb.Append("];");
                    ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "autoRegion", sb.ToString(), true);

                }

                ddl = (DropDownList)e.Row.FindControl("ddlFilter3");
                if (ddl != null)
                {
                    sb.Clear();
                    sb.Append("autoCity = [");
                    for (int i = 1; i < ddl.Items.Count; ++i)
                    {
                        if (i != 1)
                            sb.Append(",");
                        sb.Append("\"" + ddl.Items[i].Text + "\"");
                    }
                    sb.Append("];");
                    ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "autoCity", sb.ToString(), true);
                }
            }
            else if (e.Row.RowType == DataControlRowType.DataRow)
            {
                if (IsEmptyGrid)
                    e.Row.Attributes.Add("style", "display:none;");

                DropDownList ddl = (DropDownList)e.Row.FindControl("ddlSaleChannelId");
                if (ddl != null)
                {
                    using (var repo = RepositoryFactory.ResolveRepository<SetupRepository>())
                    {
                        ddl.DataSource = repo.SaleChannelCombo(false, true);
                        ddl.DataBind();
                    }
                    ddl.SelectedValue = drv["SaleChannelId"].ToString();
                }

                ddl = (DropDownList)e.Row.FindControl("ddlStorePointId");
                if (ddl != null)
                {
                    using (var repo = RepositoryFactory.ResolveRepository<SetupRepository>())
                    {
                        ddl.DataSource = repo.StorePointCombo(false, true);
                        ddl.DataBind();
                    }
                    ddl.SelectedValue = drv["StorePointId"].ToString();
                }

                for (int i = 1; i <= 5; ++i)
                {
                    string nameColumn = "Depart" + i.ToString();
                    ddl = (DropDownList)e.Row.FindControl("ddl" + nameColumn + "UserId");
                    if (ddl != null)
                    {
                        using (var repo = RepositoryFactory.ResolveRepository<UserRepository>())
                        {
                            Depart depart = null;
                            using (var repo1 = RepositoryFactory.ResolveRepository<SetupRepository>())
                            {
                                depart = repo1.FindDepartByColumnName(nameColumn);
                            }
                            if (depart != null)
                            {
                                //ограничиваем селекты по рангу должности
                                IEnumerable<User> users = repo.UserComboByDepart(depart.DepartId, false, true);
                                if (!IsAdmin && !CurrentUser.IsFullAccess && CurrentUserDepart.OrderNumber >= depart.OrderNumber)
                                {
                                    ddl.DataSource = users.Where(u => u.Id.ToString() == drv[nameColumn + "UserId"].ToString());
                                }
                                else
                                {
                                    ddl.DataSource = users;
                                    
                                }
                                ddl.DataBind();
                                ddl.SelectedValue = drv[nameColumn + "UserId"].ToString();  
                            }
                        }
                    }
                }

                TextBox tb = (TextBox)e.Row.FindControl("txtRegionName");
                if (tb != null)
                    ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "autoRegionBind" + e.Row.RowIndex.ToString(), string.Format(" $('#{0}').autocomplete({{ source: autoRegion }});", tb.ClientID), true);
                tb = (TextBox)e.Row.FindControl("txtCityName");
                if (tb != null)
                    ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "autoCityBind" + e.Row.RowIndex.ToString(), string.Format(" $('#{0}').autocomplete({{ source: autoCity }});", tb.ClientID), true);
            } 
        }

        protected void btnSaveAP_Click(object sender, EventArgs e)
        {
            string result = SaveAP();
            if (!string.IsNullOrEmpty(result))
            {
                ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "alert", string.Format("alert('{0}');", result), true);
                return;
            }
            else
            {
                LoadDataFromDb();
                LoadData();
                ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "alert", "alert('Сохранение успешно завершено!');", true);
            }
        }

        protected void btnAddAP_Click(object sender, EventArgs e)
        {
            string result = SaveAP();
            if (!string.IsNullOrEmpty(result))
            {
                ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "alert", string.Format("alert('{0}');", result), true);
                return;
            }

            List<KeyValuePair<string, object>> data = new List<KeyValuePair<string, object>>();
            data.Add(new KeyValuePair<string, object>("RegionName", "-"));
            data.Add(new KeyValuePair<string, object>("CityName", "-"));
            data.Add(new KeyValuePair<string, object>("SalePointName", "-"));
            if (!IsAdmin && IsAuthentificated)
            {
                data.Add(new KeyValuePair<string, object>(String.Concat(CurrentUserDepart.APColumnName,"UserId"), CurrentUser.Id));
            }
            APRepository.EditAP(-1, data);

            LoadDataFromDb();
            LoadData();
        }

        protected string SaveAP()
        {
            string result = "";

            // Первый проход - проверка ошибок
            foreach (GridViewRow item in grid.Rows)
            {
                if (item.RowType != DataControlRowType.DataRow)
                    continue;
                if (grid.DataKeys[item.RowIndex]["APId"] == System.DBNull.Value)
                    continue;

                int id = Convert.ToInt32(grid.DataKeys[item.RowIndex]["APId"]);
                string clientId = ((TextBox)item.FindControl("txtClientId")).Text;
                if (!string.IsNullOrEmpty(clientId))
                    if (!APRepository.CheckClientId(id, clientId))
                    {
                        return string.Format("Id {0} уже используется!", clientId);
                    }
            }

            // Второй проход - сохранение
            foreach (GridViewRow item in grid.Rows)
            {
                if (item.RowType != DataControlRowType.DataRow)
                    continue;
                if (grid.DataKeys[item.RowIndex]["APId"] == System.DBNull.Value)
                    continue;

                DropDownList ddl;
                int id = Convert.ToInt32(grid.DataKeys[item.RowIndex]["APId"]);
                List<KeyValuePair<string, object>> data = new List<KeyValuePair<string, object>>();
                data.Add(new KeyValuePair<string, object>("ClientId", ((TextBox)item.FindControl("txtClientId")).Text));
                data.Add(new KeyValuePair<string, object>("RegionName", ((TextBox)item.FindControl("txtRegionName")).Text));
                data.Add(new KeyValuePair<string, object>("CityName", ((TextBox)item.FindControl("txtCityName")).Text));
                data.Add(new KeyValuePair<string, object>("SalePointName", ((TextBox)item.FindControl("txtSalePointName")).Text));
                data.Add(new KeyValuePair<string, object>("SalePointJurName", ((TextBox)item.FindControl("txtSalePointJurName")).Text));
                data.Add(new KeyValuePair<string, object>("Address", ((TextBox)item.FindControl("txtAddress")).Text));
                data.Add(new KeyValuePair<string, object>("TTQty", ((TextBox)item.FindControl("txtTTQty")).Text));
                ddl = (DropDownList)item.FindControl("ddlSaleChannelId");
                data.Add(new KeyValuePair<string, object>("SaleChannelId", ddl.SelectedValue == "-1" ? null : ddl.SelectedValue));
                ddl = (DropDownList)item.FindControl("ddlStorePointId");
                data.Add(new KeyValuePair<string, object>("StorePointId", ddl.SelectedValue == "-1" ? null : ddl.SelectedValue));
                
                for (int i = 1; i <= 5; ++i)
                {
                    ddl = (DropDownList)item.FindControl("ddlDepart" + i.ToString() + "UserId");
                    if ((ddl != null) && (ddl.Items.Count > 0))
                        data.Add(new KeyValuePair<string, object>("Depart" + i.ToString() + "UserId", ddl.SelectedValue == "-1" ? null : ddl.SelectedValue));
                }
                for (int i = 0; i < CustomColumnsList.Count; ++i)
                {
                    data.Add(new KeyValuePair<string, object>("Custom " + CustomColumnsList[i], ((TextBox)item.FindControl("txtCustom" + i.ToString())).Text));
                }

                data.Add(new KeyValuePair<string, object>("LastUpdateUserName", User.Identity.Name));
                data.Add(new KeyValuePair<string, object>("LastUpdateDateTime", DateTime.Now));

                APRepository.EditAP(id, data);
            }

            return result;
        }
        
        protected void grid_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            int id;

            /*switch (e.CommandName)
            {
                case "MakeDelete":
                    string result = SaveAP();
                    if (!string.IsNullOrEmpty(result))
                    {
                        ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "alert", string.Format("alert('{0}');", result), true);
                        return;
                    }

                    id = Convert.ToInt32(e.CommandArgument.ToString());
                    APRepository.DeleteAP(id.ToString());

                    LoadDataFromDb();
                    LoadData();
                    
                    break;
            }*/
        }

        protected void btnDeleteSelected_Click(object sender, EventArgs e)
        {
            string result = SaveAP();
            if (!string.IsNullOrEmpty(result))
            {
                ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "alert", string.Format("alert('{0}');", result), true);
                return;
            }

            DataRow[] drs = GridData.Select("IsSelected = 1");
            if (drs.Length == 0)
            {
                ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "alert", "alert('Нет выбранных строк!');", true);
                return;
            }

            string ids = "";
            foreach (DataRow dr in drs)
                ids += dr["APId"].ToString() + ";";

            APRepository.DeleteAP(ids);

            LoadDataFromDb();
            LoadData();
        }

        public override void ExcelImport(FileUpload fu)
        {
            if (!fu.HasFile)
            {
                ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "alert", string.Format("alert('{0}');", "Не указан файл!"), true);
                return;
            }

            try
            {
                using (ExcelPackage pck = new ExcelPackage(fu.PostedFile.InputStream))
                {
                    var workBook = pck.Workbook;
                    if (workBook == null)
                    {
                        ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "alert", string.Format("alert('{0}');", "Файл не содержит листов!"), true);
                        return;
                    }

                    if (workBook.Worksheets.Count == 0)
                    {
                        ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "alert", string.Format("alert('{0}');", "Файл не содержит листов!"), true);
                        return;
                    }
                          
                    var ws = workBook.Worksheets.First();

                    DataTable dtColumnError = new DataTable();
                    dtColumnError.Columns.Add("ColumnName", typeof(string));
                    dtColumnError.Columns.Add("FileColumnName", typeof(string));

                    // Проверяем названия колонок
                    for (int i = 0; i < ColumnsList.Count; ++i)
                    {
                        string cellValue = "";
                        if (ws.Cells[1, i + 1].Value != null)
                            cellValue = ws.Cells[1, i + 1].Value.ToString().Trim();
                        dtColumnError.LoadDataRow(new object[] { ColumnsList[i].HeaderText, cellValue }, true);


                        /*if ((ws.Cells[1, i + 1].Value == null) || string.IsNullOrEmpty(ws.Cells[1, i + 1].Value.ToString()))
                        {
                            ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "alert", string.Format("alert('{0}');", "Поля в файле не совпадают с полями в таблице!"), true);
                            return;
                        }

                        if (ColumnsList[i].HeaderText.ToLower() != ws.Cells[1, i + 1].Value.ToString().ToLower())
                        {
                            ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "alert", string.Format("alert('{0}');", "Поля в файле не совпадают с полями в таблице!"), true);
                            return;
                        }*/
                    }
                    if (dtColumnError.Select("ColumnName <> FileColumnName").Count() > 0)
                    {
                        ctrlExcelImportColumnErrorList.LoadData(dtColumnError);
                        ctrlExcelImportColumnErrorList.ShowDialog();
                        //ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "showExcelImportErrorList", "showExcelImportErrorList();", true);
                        return;
                    }

                    DataTable dtClientId = new DataTable();
                    dtClientId.Columns.Add("RowNum", typeof(int));
                    dtClientId.Columns.Add("ClientId", typeof(string));

                    DataTable dtError = new DataTable();
                    dtError.Columns.Add("RowNum", typeof(int));
                    dtError.Columns.Add("ColumnName", typeof(string));
                    dtError.Columns.Add("ErrorMessage", typeof(string));
                    dtError.Columns.Add("Value", typeof(string));
                    
                    // Проверяем данные (первый проход)
                    for (int j = 2; j <= ws.Dimension.End.Row; ++j)
                    {
                        for (int i = 0; i < ColumnsList.Count; ++i)
                        {
                            string cellValue = "";
                            if (ws.Cells[j, i + 1].Value != null)
                                cellValue = ws.Cells[j, i + 1].Value.ToString().Trim();

                            // проверка обязательных полей
                            if (ColumnsList[i].DataField.Trim().Equals("RegionName") || ColumnsList[i].DataField.Trim().Equals("CityName") || ColumnsList[i].DataField.Trim().Equals("SalePointName"))
                            {
                                if (string.IsNullOrEmpty(cellValue))
                                    dtError.LoadDataRow(new object[] { j, ColumnsList[i].HeaderText, "Значение колонки не может быть пустым", "" }, true);
                            }

                            // проверка ClientId
                            if (ColumnsList[i].DataField.Trim().Equals("ClientId"))
                            {
                                if (!string.IsNullOrEmpty(cellValue))
                                {
                                    if (APRepository.CheckClientId(-1, cellValue))
                                    {
                                        DataRow[] drs = dtClientId.Select(string.Format("ClientId = '{0}'", cellValue));
                                        if (drs.Length > 0)
                                            dtError.LoadDataRow(new object[] { j, ColumnsList[i].HeaderText.Trim(), string.Format("Значение повторяется в файле, строка {0}", drs[0]["RowNum"].ToString()), cellValue }, true);
                                        else
                                            dtClientId.LoadDataRow(new object[] { j, cellValue }, true);
                                    }
                                    else
                                        dtError.LoadDataRow(new object[] { j, ColumnsList[i].HeaderText, "Значение уже существует в базе", cellValue }, true);
                                }
                            }

                            // проверка справочников
                            if (ColumnsList[i].DataField.Trim().Equals("SaleChannelName") && (!string.IsNullOrEmpty(cellValue)))
                            {
                                using (var repo = RepositoryFactory.ResolveRepository<SetupRepository>())
                                {
                                    if (repo.FindSaleChannel(cellValue) == null)
                                        dtError.LoadDataRow(new object[] { j, ColumnsList[i].HeaderText.Trim(), "Значение не найдено в справочнике", cellValue }, true);
                                }
                            }
                            if (ColumnsList[i].DataField.Trim().Equals("StorePointName") && (!string.IsNullOrEmpty(cellValue)))
                            {
                                using (var repo = RepositoryFactory.ResolveRepository<SetupRepository>())
                                {
                                    if (repo.FindStorePoint(cellValue) == null)
                                        dtError.LoadDataRow(new object[] { j, ColumnsList[i].HeaderText.Trim(), "Значение не найдено в справочнике", cellValue }, true);
                                }
                            }
                            for (int k = 1; k <= 5; ++k)
                            {
                                string departColumnName = "Depart" + k.ToString();
                                string departUserColumnName = departColumnName + "UserName";
                                if (ColumnsList[i].DataField.Trim().Equals(departUserColumnName) && (!string.IsNullOrEmpty(cellValue)))
                                {
                                    using (var repo = RepositoryFactory.ResolveRepository<SetupRepository>())
                                    {
                                        Depart d = repo.FindDepartByColumnName(departColumnName);
                                        using (var repoUser = RepositoryFactory.ResolveRepository<UserRepository>())
                                        {
                                            if (repoUser.UserComboByDepart(d.DepartId, false, false).FirstOrDefault(u => u.FullName.Trim().ToLower().Equals(cellValue.ToLower())) == null)
                                                dtError.LoadDataRow(new object[] { j, ColumnsList[i].HeaderText.Trim(), "Значение не найдено в справочнике", cellValue }, true);
                                        }
                                    }
                                }
                            }
                        } // for ColumnList
                    } // for excel rows

                    if (dtError.Rows.Count > 0)
                    {
                        ctrlExcelImportErrorList.LoadData(dtError);
                        ctrlExcelImportErrorList.ShowDialog();
                        //ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "showExcelImportErrorList", "showExcelImportErrorList();", true);
                        
                        return;

                    }
                    else
                    {
                        // Если не было ошибок делаем импорт
                        for (int j = 2; j <= ws.Dimension.End.Row; ++j)
                        {
                            int id = -1;
                            List<KeyValuePair<string, object>> data = new List<KeyValuePair<string, object>>();
                                                        
                            data.Add(new KeyValuePair<string, object>("LastUpdateUserName", User.Identity.Name));
                            data.Add(new KeyValuePair<string, object>("LastUpdateDateTime", DateTime.Now));

                            for (int i = 0; i < ColumnsList.Count; ++i)
                            {
                                string cellValue = "";
                                if (ws.Cells[j, i + 1].Value != null)
                                    cellValue = ws.Cells[j, i + 1].Value.ToString().Trim();

                                bool isCatalog = false;
                                // учитываем нормализацию справочников
                                if (ColumnsList[i].DataField.Trim().Equals("SaleChannelName"))
                                {
                                    using (var repo = RepositoryFactory.ResolveRepository<SetupRepository>())
                                    {
                                        data.Add(new KeyValuePair<string, object>("SaleChannelId", string.IsNullOrEmpty(cellValue) ? null : (object)repo.FindSaleChannel(cellValue).SaleChannelId));
                                    }
                                    isCatalog = true;
                                }
                                if (ColumnsList[i].DataField.Trim().Equals("StorePointName"))
                                {
                                    using (var repo = RepositoryFactory.ResolveRepository<SetupRepository>())
                                    {
                                        data.Add(new KeyValuePair<string, object>("StorePointId", string.IsNullOrEmpty(cellValue) ? null : (object)repo.FindStorePoint(cellValue).StorePointId));
                                    }
                                    isCatalog = true;
                                }
                                for (int k = 1; k <= 5; ++k)
                                {
                                    string departColumnName = "Depart" + k.ToString();
                                    string departUserColumnName = departColumnName + "UserName";
                                    string departUserColumnId = departColumnName + "UserId";

                                    if (ColumnsList[i].DataField.Trim().Equals(departUserColumnName))
                                    {
                                        using (var repo = RepositoryFactory.ResolveRepository<SetupRepository>())
                                        {
                                            Depart d = repo.FindDepartByColumnName(departColumnName);
                                            using (var repoUser = RepositoryFactory.ResolveRepository<UserRepository>())
                                            {
                                                int userId = -1;
                                                if (!string.IsNullOrEmpty(cellValue))
                                                    userId = repoUser.UserComboByDepart(d.DepartId, false, false).FirstOrDefault(u => u.FullName.Trim().ToLower().Equals(ws.Cells[j, i + 1].Value.ToString().Trim().ToLower())).Id;
                                                data.Add(new KeyValuePair<string, object>(departUserColumnId, string.IsNullOrEmpty(cellValue) ? null : (object)userId));
                                                isCatalog = true;
                                                break;
                                            }
                                        }
                                    }
                                }

                                if (!isCatalog)
                                    data.Add(new KeyValuePair<string, object>(ColumnsList[i].DataField.Trim(), cellValue));
                            }

                            APRepository.EditAP(id, data);

                        }
                    }
                    
                }

                LoadDataFromDb();
                LoadData();
            }
            catch (Exception e)
            {
                ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "alert", string.Format("alert('{0}');", "Не удалось обработать файл!"), true);
                return;
            }
        }
    }
}